/**
 * 
 */
package com.wubo.common.jdbc;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.wubo.common.util.ClassUtils;

/**
 * @author WuBo
 * @CreateDate 2010-9-10
 * @version 1.0.01
 */
public class SQLExecutor {
	
	private DBConfig dbconfig;
	private DataSource dataSource;
	
	public SQLExecutor(String config){
		this(new DBConfig(config));
	}
	
	public SQLExecutor(DBConfig dbconfig){
		this.dbconfig = dbconfig;
		try{
			Class.forName(dbconfig.getDriver());
		}catch(Exception e){
			throw new ExceptionInInitializerError(e);
		}
	}
	
	public SQLExecutor(DataSource dataSource){
		this.dataSource = dataSource;
	}

	public DataSource getDataSource() {
		return dataSource;
	}


	private Connection conn;
	
	private boolean readOnly;
	
	private boolean autoCommit;
	
	private String catalog;
	
	private int transactionIsolation = 2;
	
	public boolean isReadOnly() {
		return readOnly;
	}

	public void setReadOnly(boolean readOnly) {
		this.readOnly = readOnly;
	}

	public boolean isAutoCommit() {
		return autoCommit;
	}

	public void setAutoCommit(boolean autoCommit) {
		this.autoCommit = autoCommit;
	}
	
	public String getCatalog() {
		return catalog;
	}

	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}

	public int getTransactionIsolation() {
		return transactionIsolation;
	}

	public void setTransactionIsolation(int transactionIsolation) {
		this.transactionIsolation = transactionIsolation;
	}

	/**
	 * 执行sql
	 * @param sql 脚本
	 * @return 执行结果成功与否
	 */
	public boolean execute(final String sql,final Object... params) {
		return (Boolean) doExecute(sql, new PreparedStatementCallback(){
			public Object execute(Connection conn, PreparedStatement stmt)
			throws SQLException {
				setParameters(stmt, params);
				return stmt.execute();
			}
		});
	}
	
	public Object execute(String sql, PreparedStatementCallback callback){
		return doExecute(sql, callback);
	}
	
	public Object execute(ConnectionCallback callback){
		return doExecute(callback);
	}
	
	private Object doExecute(ConnectionCallback callback){
		Object result = null;
		try {
			conn = getConnection();
			
			beforeExecute(conn);
			
			result = callback.execute(conn);
			
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				System.out.println(e1.getMessage());
			}
			throw new RuntimeException(e);
		}finally{
			try {
				if(conn != null)conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return result;
	}
	
	private Object doExecute(String sql, PreparedStatementCallback callback) {
		if(sql == null) return null;
		PreparedStatement stmt = null;
		Object result = null;
		try {
			conn = getConnection();
			
			beforeExecute(conn);
			
			stmt = conn.prepareStatement(sql);
			result = callback.execute(conn, stmt);
			
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				System.out.println(e1.getMessage());
			}
			throw new RuntimeException(e);
		}finally{
			try {
				if(stmt != null)stmt.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return result;
	}
	
	protected void beforeExecute(Connection conn) throws SQLException {
		conn.setAutoCommit(autoCommit);
		conn.setReadOnly(readOnly);
		conn.setTransactionIsolation(transactionIsolation);
		if(catalog != null) conn.setCatalog(catalog);
	}
	
	protected void setParameters(PreparedStatement ps, Object[] params)
	throws SQLException {
		for(int i=0; i<params.length; i++){
			setValue(ps, params[i], i+1);
		}
	}
	
	public Connection getCurrentConnection(){
		return conn;
	}
	
	private Connection getConnection() throws SQLException {
		if(conn == null || conn.isClosed()){
			if(dataSource != null){
				conn = dataSource.getConnection();
			}else if(dbconfig != null){
				conn = DriverManager.getConnection(dbconfig.getUrl()
					, dbconfig.getUsername()
					, dbconfig.getPassword());
			}
		}
		return conn;
	}
	
	protected void setValue(PreparedStatement ps , Object value, int index)
	throws SQLException {
		if(value == null){
			ps.setNull(index, java.sql.Types.NULL);
			return;
		}
		Class<?> type = value.getClass();
		if(ClassUtils.isPrimitiveWrapper(type)){
			type = ClassUtils.resolvePrimitiveClassName(type);
		}
		if(int.class.isAssignableFrom(type)){
			ps.setInt(index, (Integer)value);
		}else if(String.class.isAssignableFrom(type)){
			ps.setString(index, (String)value);
		}else if(long.class.isAssignableFrom(type)){
			ps.setLong(index, (Long)value);
		}else if(boolean.class.isAssignableFrom(type)){
			ps.setBoolean(index, (Boolean)value);
		}else if(double.class.isAssignableFrom(type)){
			ps.setDouble(index, (Double)value);
		}else if(char.class.isAssignableFrom(type)){
			ps.setString(index, (Character)value+"");
		}else if(short.class.isAssignableFrom(type)){
			ps.setShort(index, (Short)value);
		}else if(float.class.isAssignableFrom(type)){
			ps.setFloat(index, (Float)value);
		}else if(byte.class.isAssignableFrom(type)){
			ps.setByte(index, (Byte)value);
		}else if(type.isArray()){
			ps.setArray(index, (Array)value);
		}else if(value instanceof java.sql.Timestamp){
			ps.setTimestamp(index, (java.sql.Timestamp) value);
		}else if(value instanceof java.sql.Date){
			ps.setDate(index, (java.sql.Date)value );
		}else if(value instanceof java.util.Date){
			ps.setDate(index, new java.sql.Date( ((java.util.Date)value).getTime() ));
		}else if(value instanceof java.sql.Time){
			ps.setTime(index, (java.sql.Time)value );
		}
	}
	
	public static interface PreparedStatementCallback{
		Object execute(Connection conn, PreparedStatement stmt) throws SQLException;
	}
	
	public static interface ConnectionCallback{
		Object execute(Connection conn) throws SQLException;
	}
}
